﻿using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using TCL.EO;
using TCL.FUNC;

namespace TCL.DB
{
    public class MSSqlDataProvider : DataAccessObject
    {
        #region "Properties"
        public virtual Boolean bWriteLog { get; set; }
        public virtual String sConnectionStrings { get; set; }
        #endregion

        #region DB Access Functions
        //protected String getConnectionString()
        //{
        //    String sRet = "";
        //    String strCSS = SystemEx.getAppSettings("NMT_CSS", "");
        //    if (!String.IsNullOrEmpty(strCSS))
        //    {
        //        strCSS = SecurityEx.DecryptString(strCSS, SecurityEx.Base64Encode(Environment.MachineName));
        //        String[] arrCon = StringEx.Trim(strCSS).Split('|');
        //        if (arrCon.Length >= 5)
        //        {
        //            sRet = "Data Source={0};Initial Catalog={1};Persist Security Info=True;User ID={2};Password={3}";
        //            sRet = String.Format(sRet, arrCon[0], arrCon[1], arrCon[2], arrCon[3]);
        //        }
        //    }
        //    else
        //        sRet = ConfigurationManager.ConnectionStrings[sConnectionStrings].ConnectionString;
        //    return sRet;
        //}

        protected void SetConnection(ref SqlCommand cmdSql)
        {
            SqlConnection connMSSql = new SqlConnection(getConnectionString(sConnectionStrings));
            if (connMSSql.State != ConnectionState.Open)
            {
                connMSSql.Open();
            }
            cmdSql.Connection = connMSSql;
        }

        public DataSet GetDataSet(SqlCommand cmdSql)
        {
            LogEO objLog = new LogEO(cmdSql, sConnectionStrings, ConstEx.Log_iType.DATABASE_SELECT);
            try
            {
                using (DataSet ds = new DataSet())
                {
                    using (SqlDataAdapter da = new SqlDataAdapter())
                    {
                        SetConnection(ref cmdSql);
                        da.SelectCommand = cmdSql;
                        da.Fill(ds);
                        objLog.bStatus = true;
                        return ds;
                    }
                }
            }
            finally
            {
                if (cmdSql.Connection.State == ConnectionState.Open)
                    cmdSql.Connection.Close();
                cmdSql.Dispose();
                if (bWriteLog)
                    (new Logs()).WriteLog_To_DB(objLog, true);
            }
        }

        public DataTable GetDataTable(SqlCommand cmdSql)
        {
            return GetDataSet(cmdSql).Tables[0];
        }

        public SqlDataReader GetReadOnlyData(SqlCommand cmdSql)
        {
            LogEO objLog = new LogEO(cmdSql, sConnectionStrings, ConstEx.Log_iType.DATABASE_OTHER);
            try
            {
                SetConnection(ref cmdSql);
                SqlDataReader dr = cmdSql.ExecuteReader();
                objLog.bStatus = true;
                return dr;
            }
            finally
            {
                if (cmdSql.Connection.State == ConnectionState.Open)
                    cmdSql.Connection.Close();
                cmdSql.Dispose();
                if (bWriteLog)
                    (new Logs()).WriteLog_To_DB(objLog, true);
            }
        }

        protected Int32 ExecuteNonQuery(SqlCommand cmdSql, Boolean isGetRetVal = false)
        {
            LogEO objLog = new LogEO(cmdSql, sConnectionStrings, ConstEx.Log_iType.DATABASE_INSERT_UPDATE_DELETE);
            Int32 iRowsAffected = 0;
            try
            {
                SetConnection(ref cmdSql);
                SqlParameter param = cmdSql.Parameters.Add("RetVal", SqlDbType.Int);
                param.Direction = ParameterDirection.ReturnValue;
                iRowsAffected = cmdSql.ExecuteNonQuery();
                if (isGetRetVal)
                    Int32.TryParse(Convert.ToString(param.Value), out iRowsAffected);
                objLog.bStatus = true;
            }
            finally
            {
                if (cmdSql.Connection.State == ConnectionState.Open)
                    cmdSql.Connection.Close();
                cmdSql.Dispose();
                if (bWriteLog)
                    (new Logs()).WriteLog_To_DB(objLog, true);
            }
            return iRowsAffected;
        }

        public Boolean ExecuteNonQuery_RetBool(SqlCommand cmdSql, Boolean isGetRetVal = false)
        {
            if (ExecuteNonQuery(cmdSql, isGetRetVal) > 0)
                return true;
            else
                return false;
        }

        public Int32 ExecuteNonQuery_RetInt32(SqlCommand cmdSql)
        {
            return ExecuteNonQuery(cmdSql, true);
        }

        public Object ExecuteScalar(SqlCommand cmdSql)
        {
            LogEO objLog = new LogEO(cmdSql, sConnectionStrings, ConstEx.Log_iType.DATABASE_OTHER);
            try
            {
                SetConnection(ref cmdSql);
                objLog.bStatus = true;
                return cmdSql.ExecuteScalar();
            }
            finally
            {
                if (cmdSql.Connection.State == ConnectionState.Open)
                    cmdSql.Connection.Close();
                cmdSql.Dispose();
                if (bWriteLog)
                    (new Logs()).WriteLog_To_DB(objLog, true);
            }
        }

        //public Int32 DBSize()
        //{
        //    using (SqlCommand cmd = new SqlCommand("select sum(size) * 8 * 1024 from sysfiles"))
        //    {
        //        cmd.CommandType = CommandType.Text;
        //        return (int)ExecuteScalar(cmd);
        //    }
        //}

        public DataTable getDatabaseSizeCurrent(String DATABASE_NAME)
        {
            DataTable dt = new DataTable();
            SqlCommand cmd = new SqlCommand("getDatabaseSizeCurrent");
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add(new SqlParameter("@DATABASE_NAME", DATABASE_NAME));
            return GetDataTable(cmd);
            return null;
        }

        public bool CheckConnect()
        {
            SqlCommand cmd = new SqlCommand("select getdate()");
            if (CheckEx.IsDataTable(GetDataTable(cmd)))
                return true;
            else
                return false;
        }

        public Boolean Backup_Database(String LinkBackup)
        {
            SqlCommand cmd = new SqlCommand("BACKUP DATABASE [NguyenManhThang] TO DISK='" + LinkBackup + "'");
            return ExecuteNonQuery_RetBool(cmd);
        }

        public Boolean Restore_Database(String LinkRestore)
        {
            SqlCommand cmd = new SqlCommand("USE MASTER RESTORE DATABASE [NguyenManhThang] FROM DISK='" + LinkRestore + "'");
            return ExecuteNonQuery_RetBool(cmd);
        }

        public DateTime Get_DateTime_Now()
        {
            DateTime tRet = new DateTime();
            SqlCommand cmdSql = new SqlCommand("SELECT GETDATE();");
            try
            {
                cmdSql.CommandType = CommandType.Text;
                SetConnection(ref cmdSql);
                tRet = Convert.ToDateTime(cmdSql.ExecuteScalar());
            }
            catch
            {
                tRet = DateTime.Now;
            }
            finally
            {
                if (cmdSql.Connection.State == ConnectionState.Open)
                    cmdSql.Connection.Close();
                cmdSql.Dispose();
            }
            return tRet;
        }
        #endregion

        #region DB Access Functions With Tran
        protected void ExecuteNonQuery_Tran(SqlCommand cmdSql, SqlConnection connSql, SqlTransaction tranSql, ref Int64 iNewID, CommandType typeSql = CommandType.StoredProcedure)
        {
            SqlParameter param = cmdSql.Parameters.Add("RetVal", SqlDbType.BigInt);
            param.Direction = ParameterDirection.ReturnValue;
            cmdSql.Connection = connSql;
            cmdSql.Transaction = tranSql;
            cmdSql.CommandType = typeSql;
            cmdSql.ExecuteNonQuery();
            Int64.TryParse(Convert.ToString(param.Value), out iNewID);
        }

        #endregion
    }
}
